package com.bj58.groupbuy.action.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.bj58.groupbuy.action.db.DBConfig;
import com.bj58.groupbuy.action.db.DBConnectionPool;
import com.bj58.groupbuy.action.db.JdbcUitl;
import com.bj58.groupbuy.action.model.Displocal;
import com.bj58.groupbuy.action.service.DisplocalService;

public class DisplocalServiceImpl implements DisplocalService {
	
	@Override
	public List<Displocal> getListByOne(String city1) throws Exception {
		String sql = "SELECT city_name_en,city1_name  FROM data_dict.m_common_disp_city where city1_name = '"+city1+"' and city2_id is null";
		List<String> cols = new ArrayList<String>();
		cols.add("ListName");	cols.add("LocalName");
		List<Displocal>  rsList = new ArrayList<Displocal>();
		Connection con = DBConnectionPool.getConnection(DBConfig.db_131_data_bi.getIntType());
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				Displocal  vo = new Displocal();
				vo.setListName(rs.getString(1));
				vo.setLocalName(rs.getString(2));
				rsList.add(vo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUitl.closeResultSet(rs); JdbcUitl.closeStatement(st); JdbcUitl.closeConnection(con);
		}
		return rsList;
	}
    /**
     * 根据所给城市名，获取对应的编号
     * */
	@Override
	public String getcityname(String city) throws Exception {
		String sql = "SELECT city_name_en FROM data_dict.m_common_disp_city where city1_name = '"+city+"' and city2_id is null";
		Connection con = DBConnectionPool.getConnection(DBConfig.db_131_data_bi.getIntType());
		Statement st = null;
		ResultSet rs = null;
		String cityname="";
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				cityname=rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUitl.closeResultSet(rs); JdbcUitl.closeStatement(st); JdbcUitl.closeConnection(con);
		}
		return cityname;
	}
	 /**
     * 获取所有城市名。
     * */
	@Override
	public List<String> getallcityname() throws Exception {
		String sql = "SELECT city1_name FROM data_dict.m_common_disp_city where city1_name is not null and city2_id is null";
		Connection con = DBConnectionPool.getConnection(DBConfig.db_131_data_bi.getIntType());
		Statement st = null;
		ResultSet rs = null;
		List<String> allcityname=new ArrayList<String>();
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()) {
				allcityname.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUitl.closeResultSet(rs); JdbcUitl.closeStatement(st); JdbcUitl.closeConnection(con);
		}
		return allcityname;
	}

}
